"""Create initial database schema

Revision ID: 919b4b83d8c3
Revises: 
Create Date: 2025-07-04 23:21:06.916439

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = '919b4b83d8c3'
down_revision: Union[str, Sequence[str], None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('counterparty',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(), nullable=False, comment='对手方名称，如“张三”或“京东商城”'),
    sa.Column('account_number', sa.String(), nullable=True, comment='对手方的账号'),
    sa.Column('counterparty_type', sa.String(), nullable=False, comment='对手类型，如“PERSON”或“MERCHANT”'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_counterparty_account_number'), 'counterparty', ['account_number'], unique=False)
    op.create_index(op.f('ix_counterparty_id'), 'counterparty', ['id'], unique=False)
    op.create_index(op.f('ix_counterparty_name'), 'counterparty', ['name'], unique=False)
    op.create_table('person',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('full_name', sa.String(), nullable=False),
    sa.Column('id_type', sa.String(), nullable=True),
    sa.Column('id_number', sa.String(), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_person_full_name'), 'person', ['full_name'], unique=False)
    op.create_index(op.f('ix_person_id'), 'person', ['id'], unique=False)
    op.create_table('account',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('account_name', sa.String(), nullable=False, comment='账户的自定义名称，如“招行工资卡”'),
    sa.Column('account_number', sa.String(), nullable=False, comment='本方账户的完整账号或卡号（明文存储）'),
    sa.Column('account_type', sa.String(), nullable=True, comment='账户类型，如“储蓄卡”、“信用卡”'),
    sa.Column('institution', sa.String(), nullable=True, comment='所属金融机构，如“招商银行”'),
    sa.Column('owner_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['owner_id'], ['person.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('account_number')
    )
    op.create_index(op.f('ix_account_account_name'), 'account', ['account_name'], unique=False)
    op.create_index(op.f('ix_account_id'), 'account', ['id'], unique=False)
    op.create_table('file_metadata',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('filename', sa.String(), nullable=False),
    sa.Column('file_path', sa.String(), nullable=False),
    sa.Column('file_hash', sa.String(), nullable=False),
    sa.Column('filesize', sa.Integer(), nullable=False),
    sa.Column('mime_type', sa.String(), nullable=False),
    sa.Column('upload_timestamp', sa.DateTime(timezone=True), nullable=False),
    sa.Column('processing_status', sa.String(), nullable=False),
    sa.Column('error_message', sa.Text(), nullable=True),
    sa.Column('account_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['account_id'], ['account.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_file_metadata_file_hash'), 'file_metadata', ['file_hash'], unique=True)
    op.create_index(op.f('ix_file_metadata_id'), 'file_metadata', ['id'], unique=False)
    op.create_table('transaction',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('transaction_date', sa.DateTime(timezone=True), nullable=False, comment='交易发生的精确日期和时间'),
    sa.Column('amount', sa.Numeric(precision=12, scale=2), nullable=False, comment='交易金额。正数为收入，负数为支出。'),
    sa.Column('currency', sa.String(length=3), nullable=False, comment='货币类型，如“CNY”'),
    sa.Column('transaction_type', sa.String(), nullable=False, comment="交易类型, 'DEBIT' (支出) 或 'CREDIT' (收入)"),
    sa.Column('description', sa.String(), nullable=False, comment='银行提供的交易摘要或描述'),
    sa.Column('transaction_method', sa.String(), nullable=True, comment="原始交易类型或渠道，如'快捷支付'"),
    sa.Column('balance_after_txn', sa.Numeric(precision=12, scale=2), nullable=True, comment='此次交易发生后，本方账户的余额'),
    sa.Column('bank_transaction_id', sa.String(), nullable=False, comment='银行系统生成的唯一交易流水号'),
    sa.Column('is_cash', sa.Boolean(), nullable=False, comment='是否为现金交易'),
    sa.Column('location', sa.String(), nullable=True, comment='交易发生地点'),
    sa.Column('branch_name', sa.String(), nullable=True, comment='交易发生的具体网点名称'),
    sa.Column('category', sa.String(), nullable=True, comment='由系统分析得出的交易分类'),
    sa.Column('account_id', sa.Integer(), nullable=False),
    sa.Column('counterparty_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['account_id'], ['account.id'], ),
    sa.ForeignKeyConstraint(['counterparty_id'], ['counterparty.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_transaction_bank_transaction_id'), 'transaction', ['bank_transaction_id'], unique=True)
    op.create_index(op.f('ix_transaction_category'), 'transaction', ['category'], unique=False)
    op.create_index(op.f('ix_transaction_id'), 'transaction', ['id'], unique=False)
    op.create_index(op.f('ix_transaction_transaction_date'), 'transaction', ['transaction_date'], unique=False)
    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f('ix_transaction_transaction_date'), table_name='transaction')
    op.drop_index(op.f('ix_transaction_id'), table_name='transaction')
    op.drop_index(op.f('ix_transaction_category'), table_name='transaction')
    op.drop_index(op.f('ix_transaction_bank_transaction_id'), table_name='transaction')
    op.drop_table('transaction')
    op.drop_index(op.f('ix_file_metadata_id'), table_name='file_metadata')
    op.drop_index(op.f('ix_file_metadata_file_hash'), table_name='file_metadata')
    op.drop_table('file_metadata')
    op.drop_index(op.f('ix_account_id'), table_name='account')
    op.drop_index(op.f('ix_account_account_name'), table_name='account')
    op.drop_table('account')
    op.drop_index(op.f('ix_person_id'), table_name='person')
    op.drop_index(op.f('ix_person_full_name'), table_name='person')
    op.drop_table('person')
    op.drop_index(op.f('ix_counterparty_name'), table_name='counterparty')
    op.drop_index(op.f('ix_counterparty_id'), table_name='counterparty')
    op.drop_index(op.f('ix_counterparty_account_number'), table_name='counterparty')
    op.drop_table('counterparty')
    # ### end Alembic commands ###
